﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidades.Espectaculos;
using System.Configuration;
using System.Data.SqlClient;
using Entidades.General;

namespace Datos.Espectaculos
{
    public class EspConfiguracionDAL
    {

        private static EspConfiguracionDAL _instance;

        public static EspConfiguracionDAL Instance()
        {
            if (_instance == null)
            {
                _instance = new EspConfiguracionDAL();
            }

            return _instance;
        }

        public static string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        public List<EspConfiguracion> ListarCofiguracion(int IdLocal)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarConfiguracion]", con);
            com.Parameters.AddWithValue("@IdLocal", IdLocal);
            com.Parameters.AddWithValue("@mes", (object)DBNull.Value);
            com.Parameters.AddWithValue("@anio", (object)DBNull.Value);
            com.Parameters.AddWithValue("@estado", 1);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspConfiguracion> lista = new List<EspConfiguracion>();
                EspConfiguracion obj = null;
                while (dr.Read())
                {
                    obj = new EspConfiguracion();
                    obj.IdConfiguracion = Convert.ToInt32(dr["IdConfiguracion"].ToString());
                    obj.Fecha = Convert.ToDateTime(dr["Fecha"].ToString());
                    obj.IdLocal = Convert.ToInt32(dr["IdLocal"].ToString());
                    obj.Motivo = dr["Motivo"].ToString();
                    obj.Local = dr["Local"].ToString();
                    obj.Estado = Convert.ToInt32(dr["estado"].ToString());

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public List<EspConfiguracion> ListarCofiguracion(int IdLocal, int mes, int anio, int estado)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarConfiguracion]", con);
            com.Parameters.AddWithValue("@IdLocal", IdLocal == -1 ? (object)DBNull.Value : IdLocal);
            com.Parameters.AddWithValue("@mes", mes == -1 ? (object)DBNull.Value : mes);
            com.Parameters.AddWithValue("@anio", anio == -1 ? (object)DBNull.Value : anio);
            com.Parameters.AddWithValue("@estado", estado == -1 ? (object)DBNull.Value : estado);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspConfiguracion> lista = new List<EspConfiguracion>();
                EspConfiguracion obj = null;
                while (dr.Read())
                {
                    obj = new EspConfiguracion();
                    obj.IdConfiguracion = Convert.ToInt32(dr["IdConfiguracion"].ToString());
                    obj.Fecha = Convert.ToDateTime(dr["Fecha"].ToString());
                    obj.IdLocal = Convert.ToInt32(dr["IdLocal"].ToString());
                    obj.Motivo = dr["Motivo"].ToString();
                    obj.Local = dr["Local"].ToString();
                    obj.Estado = Convert.ToInt32(dr["estado"].ToString());

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public int AgregarConfiguracion(EspConfiguracion obj)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarConfiguracion]", con);

            com.Parameters.AddWithValue("@fecha", obj.Fecha);
            com.Parameters.AddWithValue("@motivo", obj.Motivo);
            com.Parameters.AddWithValue("@idLocal", obj.IdLocal);
            com.Parameters.AddWithValue("@user_creacion", obj.Auditoria.USER_CREACION);
       
            SqlParameter p = new SqlParameter();
            p.Direction = System.Data.ParameterDirection.Output;
            p.Value = 0;
            p.ParameterName = "@newIdConfiguracion";

            com.Parameters.Add(p);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    result = Convert.ToInt32(com.Parameters["@newIdConfiguracion"].Value);
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public int ModificarConfiguracion(EspConfiguracion obj)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ModificarConfiguracion]", con);
           
            com.Parameters.AddWithValue("@fecha", obj.Fecha);
            com.Parameters.AddWithValue("@motivo", obj.Motivo);
            com.Parameters.AddWithValue("@idLocal", obj.IdLocal);
            com.Parameters.AddWithValue("@user_modificacion", obj.Auditoria.USER_MODIFICACION);
            com.Parameters.AddWithValue("@estado", obj.Estado);
            com.Parameters.AddWithValue("@idConfiguracion", obj.IdConfiguracion);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public List<GenLocal> ListarLocal(int IdLocal, int estado)
        {
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarLocal]", con);
            com.Parameters.AddWithValue("@IdLocal", IdLocal == -1 ? (object)DBNull.Value : IdLocal);
            com.Parameters.AddWithValue("@estado", estado == -1 ? (object)DBNull.Value : estado);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<GenLocal> lista = new List<GenLocal>();
                GenLocal obj = null;
                while (dr.Read())
                {
                    obj = new GenLocal();
                    obj.IdLocal = Convert.ToInt32(dr["IdLocal"].ToString());
                    obj.Nombre = dr["Nombre"].ToString();
                    obj.Direccion = dr["Direccion"].ToString();
                    obj.Estado = Convert.ToBoolean(dr["Estado"].ToString())==true? 1:0;

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }
    }
}
